package jsyf.oa.dao.email;

import com.alibaba.fastjson.JSONObject;
import jsyf.com.dao.BaseJdbcDao;
import jsyf.com.util.BaseUtils;
import jsyf.oa.entity.attach.AttachModuleEntity;
import jsyf.oa.entity.email.EmailMainEntity;
import jsyf.oa.entity.email.EmailReceiverEntity;
import jsyf.oa.ientity.IBaseEntity;
import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.List;


/**
 * Created by ccp on 2017/11/1 0001.
 */
@SuppressWarnings("all")
@Repository
public class EmailDao extends BaseJdbcDao {
    private Logger logger = Logger.getLogger(EmailDao.class);

    /**
     * @description: 保存邮件主表
     * @author: @崔程鵬 @cuichengpeng
     * @creat: 9:37  2017/11/1 0001
     * @since: V1.0.0
     * @see jsyf.oa.web
     */
    public Integer addEmail(EmailMainEntity model) {
        model.setId(super.generateKey());
        model.setCreateTime(super.getCurrentTime());
        model.setUpdateTime(super.getCurrentTime());

        return super.insert("T_SYS_EMAIL", model.getJSONObject());

    }

    public Integer update(EmailMainEntity entity) {
        entity.setUpdateTime(super.getCurrentTime());
        return super.update(entity.getTableName(), entity.getJSONObject(), entity.getJSONObjectId());
    }

    /**
     * @description: 保存邮件关联表
     * @author: @崔程鵬 @cuichengpeng
     * @creat: 10:30  2017/11/23 0023
     * @since: V1.0.0
     * @see jsyf.oa.web
     */
    public Integer addEmailReceiver(EmailReceiverEntity model) {
        model.setId(super.generateKey());
        model.setReader(0);
        return super.insert("T_SYS_EMAIL_RECEIVER", model.getJSONObject());
    }

    /**
     * @description: 保存附件关联表
     * @author: @崔程鵬 @cuichengpeng
     * @creat: 11:04  2017/11/23 0023
     * @since: V1.0.0
     * @see jsyf.oa.web
     */
    public Integer addAttahModule(AttachModuleEntity model) {
        model.setId(super.generateKey());
        return super.insert("T_ATTACH_MODULE", model.getJSONObject());
    }

    /**
     * @description: 修改附件表DEL_FLAG状态
     * @author: @崔程鵬 @cuichengpeng
     * @creat: 11:04  2017/11/8 0008
     * @since: V1.0.0
     * @see jsyf.oa.web
     */
    public void attachesflag(String id) {
        JSONObject obj = new JSONObject();
        obj.put("DEL_FLAG", 0);
        JSONObject ID = new JSONObject();
        ID.put("ID", id);
        super.update("T_ATTACHMENT", obj, ID);
        BaseUtils.succsessObjRespond();
    }

    /**
     * @description: 物理删除方法
     * @author: @张巨波
     * @since: V1.0.0
     * @see jsyf.oa.web
     */
    public Integer removeByTypeAndDataId(String type, String dataId) {
        StringBuffer sql = new StringBuffer();
        sql.append(" DELETE FROM T_SYS_EMAIL WHERE E_TYPE = ? AND DATA_ID = ? \n");
        String[] params = {type, dataId};
        return super.delete(sql.toString(), params);
    }

    public List<JSONObject> getJSONListByPage(String condition, Object[] params, int requestPage, int pageSize) {
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT ");
        sql.append(" ID AS id, ");
        sql.append(" SUBJECT AS subject, ");
        sql.append(" CONTENT AS content, ");
        sql.append(" E_TYPE AS etype, ");
        sql.append(" DRAFT AS draft, ");
        sql.append(" CREATE_USER_ID AS createUserId, ");
        sql.append(" CREATE_TIME AS createTime, ");
        sql.append(" DATA_ID AS dataId, ");
        sql.append(" UPDATE_USER_ID AS updateUserId, ");
        sql.append(" UPDATE_TIME AS updateTime, ");
        sql.append(" PARENT_ID AS parentId, ");
        sql.append(" REPORT_DATE AS reportDate ");
        sql.append(" FROM ");
        sql.append(" T_SYS_EMAIL ");
        sql.append(" WHERE 1 = 1 ");
        sql.append(condition);

        super.appendPageSql(sql, requestPage, pageSize);

        List<JSONObject> jsonList = super.queryForJsonList(sql.toString(), params);

        return jsonList;
    }

    public Integer getPageCount(String condition, Object[] params) {
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT COUNT(1) AS count FROM  t_sys_email WHERE 1 = 1 ");
        sql.append(condition);
        String count = super.queryForString(sql.toString(), params);
        return Integer.parseInt(count);
    }

    public List<JSONObject> getJSONListByCondition(String condition, Object[] params) {
        return this.getJSONListByPage(condition, params, 1, Integer.MAX_VALUE);
    }

    public JSONObject getJSONById(String id) {
        List<JSONObject> jsonList = this.getJSONListByCondition(" AND ID = ? ", new Object[]{id});
        if (jsonList != null && jsonList.size() > 0) {
            return jsonList.get(0);
        } else {
            return null;
        }
    }

    public Integer removeByCondition(String condition, String[] params) {
        StringBuffer sql = new StringBuffer();
        sql.append(" DELETE FROM T_SYS_EMAIL WHERE 1 = 1 \n");
        sql.append(condition);
        return super.delete(sql.toString(), params);
    }

    public Integer everyDayEmailMove() {
        StringBuffer sql = new StringBuffer();
        StringBuffer condition = new StringBuffer();
        StringBuffer delCondition = new StringBuffer();
        List<Object> sqlArgs = new ArrayList<Object>();
        sql.append("INSERT INTO t_sys_email_all\n" +
                "SELECT *\n" +
                "FROM t_sys_email\n" +
                "WHERE DATE(t_sys_email.CREATE_TIME) <= DATE_SUB(CURDATE(),INTERVAL 6 DAY);\n");
        this.getJdbcTemplate().update(sql.toString());
        condition.append(" INSERT INTO t_sys_email_receiver_all SELECT t_sys_email_receiver.* FROM t_sys_email_receiver\n" +
                "WHERE \n" +
                "EMAIL_ID IN (SELECT ID\n" +
                "FROM t_sys_email\n" +
                "WHERE DATE(t_sys_email.CREATE_TIME) <= DATE_SUB(CURDATE(),INTERVAL 6 DAY))");
        this.getJdbcTemplate().update(condition.toString());
        delCondition.append(" DELETE FROM\n" +
                "t_sys_email\n" +
                " WHERE DATE(t_sys_email.CREATE_TIME) <= DATE_SUB(CURDATE(),INTERVAL 6 DAY);");
        Integer row = super.delete(delCondition.toString(), new String[]{});
        return row;
    }

    public List<JSONObject> getEmailByUser() {
        StringBuffer sql = new StringBuffer();
        sql.append("  SELECT\n" +
                "\tt_sys_user.ID as userId\n" +
                "FROM\n" +
                "\tt_sys_user\n" +
                "LEFT JOIN t_sys_user_leave ON t_sys_user.ID = t_sys_user_leave.USER_ID\n" +
                "LEFT JOIN t_sys_role ON t_sys_user.ROLE_ID=t_sys_role.ID\n" +
                "LEFT JOIN t_sys_role_name ON  t_sys_role.ROLE_NAME_ID=t_sys_role_name.ID\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND (\n" +
                "\tt_sys_user.JOB_STATE <> 1\n" +
                "\tOR (\n" +
                "\t\tt_sys_user.JOB_STATE = 1\n" +
                "\t\tAND t_sys_user_leave.LEAVE_DATE BETWEEN '2018-09-08' AND '2018-09-26'\n" +
                "\t)\n" +
                ") \n" +
                "AND t_sys_role_name.FRONT_FLAG=1");
        List<JSONObject> jsonList = super.queryForJsonList(sql.toString());
        return jsonList;
    }

    public List<JSONObject> getEmailReceiverAll(String condtion) {
        StringBuffer sql = new StringBuffer();
        sql.append(" select t_sys_email_receiver_all.USER_ID AS userId,\n" +
                "\tt_sys_email_receiver_all.USER_NAME AS userName,\n" +
                "\tt_sys_email_receiver_all.USER_TYPE AS userType,\n" +
                "\tt_sys_email_receiver_all.READER AS reader,\n" +
                "\tt_sys_email_receiver_all.READ_OPEN_TIME AS readOpenTime,\n" +
                "\tt_sys_email_receiver_all.READ_CLOSE_TIME AS readCloseTime,\n" +
                "\tt_sys_email_receiver_all.SENDER_ID AS senderId,\n" +
                "\tt_sys_email_receiver_all.E_TYPE AS eType,\n" +
                "\tt_sys_email_receiver_all.REPORT_DATE AS reportDate,\n" +
                "\tt_sys_email_receiver_all.DATA_ID AS dataId,\n" +
                "\tt_sys_email_receiver_all.`SUBJECT` AS subject,\n" +
                "\tt_sys_email_receiver_all.`CREATE_TIME` AS createTime from t_sys_email_receiver_all\n" +
                "LEFT JOIN t_sys_email_all ON t_sys_email_receiver_all.EMAIL_ID=t_sys_email_all.ID\n" +
                "WHERE\n" +
                "t_sys_email_all.CREATE_USER_ID='" + condtion + "'\n" +
                "AND t_sys_email_all.REPORT_DATE LIKE '%2018-09-07%'" +
                "  AND (t_sys_email_receiver_all.DATA_ID !='' OR t_sys_email_receiver_all.DATA_ID is NOT NULL)");
        List<JSONObject> jsonList = super.queryForJsonList(sql.toString());
        return jsonList;
    }

    public JSONObject getEmailId(String userId, String dateTime) {
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT\n" +
                "\tID AS emailId\n" +
                "FROM\n" +
                "\tt_sys_email_all\n" +
                "WHERE\n" +
                "\tCREATE_USER_ID = '" + userId + "'\n" +
                "AND REPORT_DATE ='" + dateTime + "' ");
        JSONObject jsonObject = super.queryForJsonObject(sql.toString());
        return jsonObject;
    }

    public Integer getEmailCount(String userId, String dateTime) {
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT COUNT(1) FROM `t_sys_email_all` where ID IN (SELECT EMAIL_ID FROM t_sys_email_receiver_all)\n" +
                "AND CREATE_USER_ID='" + userId + "' AND REPORT_DATE ='" + dateTime + "' ");
        String count = super.queryForString(sql.toString());
        return Integer.parseInt(count);
    }

    public Integer getNotReadReportByRoleName(String userId, String userIds, String dateTime, String roleNames) {
        StringBuffer sql = new StringBuffer();

        sql.append("SELECT\n" +
                "\tcount(1) AS count\n" +
                "FROM\n" +
                "\tv_sys_user\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tt_sys_email.ID AS emdilId,\n" +
                "\t\tt_sys_email.CREATE_USER_ID AS senderId\n" +
                "\tFROM\n" +
                "\t\tt_sys_email\n" +
                "\tLEFT JOIN t_sys_email_receiver ON t_sys_email_receiver.EMAIL_ID = t_sys_email.ID\n" +
                "\tWHERE\n" +
                "\t\t1 = 1\n" +
                "\tAND t_sys_email.E_TYPE = 1\n" +
                "\tAND t_sys_email_receiver.READER = 1\n" +
                "\tAND t_sys_email_receiver.USER_ID = '" + userId + "'\n" +
                "\tAND t_sys_email.REPORT_DATE = '" + dateTime + "'\n" +
                ") AS email ON email.senderId = v_sys_user.id\n" +
                "AND v_sys_user.main = 1\n" +
                "LEFT JOIN t_sys_leave ON t_sys_leave.USER_ID=v_sys_user.id AND v_sys_user.main = 1 AND t_sys_leave.LEAVE_DATE='" + dateTime + "'\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND v_sys_user.main = 1\n" +
                "AND v_sys_user.id IN (" + userIds + ")\n" +
                "AND v_sys_user.roleName IN (" + roleNames + ")\n" +
                "AND email.emdilId IS NULL\n" +
                "AND t_sys_leave.ID IS NULL\n");
        String count = super.queryForString(sql.toString(), new Object[]{});
        return Integer.parseInt(count);
    }
}

